'   This class acts as a wrapper for the SQLDMO library

Public Class clsSQLDMO

    Public Enum SQLServerSecurityType
        TrustedConnection
        IntegratedSecurity
        SQLServerSecurity
    End Enum

    Public Function GetListOfSQLServers(ByRef colSQLServers As Collection) As Boolean
        '   Returns a collection of SQL Servers found on the local network segment
        Dim iIndex As Integer
        Try
            Dim oSQLServer As New SQLDMO.SQLServer()
            Dim oServerList As SQLDMO.NameList = oSQLServer.Application.ListAvailableSQLServers
            colSQLServers = New Collection()
            For iIndex = 1 To oServerList.Count
                colSQLServers.Add(oServerList.Item(iIndex).ToString)
            Next
            Return True
        Catch ex As System.Exception
            Throw New System.Exception(ex.ToString)
            Return False
        End Try
    End Function

    Public Function GetListOfSQLServerDatabases(ByRef colSQLServerDatabases As Collection _
                                                , ByVal sSQLServer As String _
                                                , ByVal enmSecurityType As SQLServerSecurityType _
                                                , ByVal sLoginID As String _
                                                , ByVal sPassword As String _
                                                ) As Boolean
        '   Gets a list of the databases on a given SQL Server
        '   Overloaded function that is only called for SQL Security 
        Try
            Dim oSQLServer As New SQLDMO.SQLServer()
            If ConnectToSQLServer(oSQLServer, sSQLServer, enmSecurityType, sLoginID, sPassword) Then
                If PopulateCollectionWithListOfDatabases(colSQLServerDatabases, oSQLServer) Then
                    Return True
                End If
            End If
        Catch ex As System.Exception
            Throw New System.Exception(ex.ToString)
            Return False
        End Try

    End Function

    Public Function GetListOfSQLServerDatabases(ByRef colSQLServerDatabases As Collection _
                                                , ByVal sSQLServer As String _
                                                , ByVal enmSecurityType As SQLServerSecurityType _
                                                ) As Boolean
        '   Gets a list of the databases on a given SQL Server
        '   Overloaded function that is called for Trusted Connection/Integrated Security
        Try
            Dim oSQLServer As New SQLDMO.SQLServer()
            If ConnectToSQLServer(oSQLServer, sSQLServer, enmSecurityType) Then
                If PopulateCollectionWithListOfDatabases(colSQLServerDatabases, oSQLServer) Then
                    Return True
                End If
            End If
        Catch ex As System.Exception
            Throw New System.Exception(ex.ToString)
            Return False
        End Try

    End Function

    Private Function ConnectToSQLServer(ByVal oSQLServer As SQLDMO.SQLServer _
                                       , ByVal sSQLServer As String _
                                       , ByVal enmSecurityType As SQLServerSecurityType _
                                       , ByVal sLoginID As String _
                                       , ByVal sPassword As String _
                                       ) As Boolean
        '   Connects to a given SQL Server
        '   Overloaded function that is called for SQL Server Security
        With oSQLServer
            .LoginSecure = False
            .Connect(sSQLServer, sLoginID, sPassword)
        End With
        Return True
    End Function

    Private Function ConnectToSQLServer(ByVal oSQLServer As SQLDMO.SQLServer _
                                       , ByVal sSQLServer As String _
                                       , ByVal enmSecurityType As SQLServerSecurityType _
                                       ) As Boolean
        '   Connects to a given SQL Server
        '   Overloaded function that is called for Trusted Connection/Integrated Security
        With oSQLServer
            Select Case enmSecurityType
                Case SQLServerSecurityType.IntegratedSecurity
                    .LoginSecure = True
                    .Connect(sSQLServer)
                Case SQLServerSecurityType.TrustedConnection
                    .LoginSecure = True
                    .Connect(sSQLServer)
            End Select
        End With
        Return True
    End Function

    Private Function PopulateCollectionWithListOfDatabases(ByRef colSQLServerDatabases As Collection _
                                                          , ByVal oSQLServer As SQLDMO.SQLServer _
                                                          ) As Boolean
        '   Returns a collection of SQL Database names found on a given SQL Server
        Dim iIndex As Integer
        Dim oDBList As SQLDMO.Databases = oSQLServer.Databases
        For iIndex = 1 To oSQLServer.Databases.Count
            colSQLServerDatabases.Add(oDBList.Item(iIndex).Name)
        Next
        Return True
    End Function

End Class
